package com.soft.dao;

import com.soft.entity.Administrators;
import com.soft.util.DBHelper;
import com.soft.util.PageBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

//管理员
public class AdministratorsDAO {
    //获取所有管理员信息
    public Map<String, Object> getAll(PageBean pb) throws Exception {
        //预编译SQL语句对象
        //prepareStatement对象防止sql注入的方式是把用户非法输入的单引号用\反斜杠做了转义，从而达到了防止sql注入的目的
        PreparedStatement stmt = null;
        //数据库结果集的数据表
        ResultSet rs = null;
        //管理员集合
        ArrayList<Administrators> list = new ArrayList<>();

        //查询总记录数:设置到pd对象中
        int totalCount = getTotalCount();
        pb.setTotalCount(totalCount);
        //判断(预防当前页为首页或末页时,点击上一页或下一页出错)
        if (pb.getCurrentPage() <= 0) {
            //把当前页设置为1
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            //把当前页设置为最大页数
            pb.setCurrentPage(pb.getTotalPage());
        }

        //获取当前页数:计算查询的起始行,返回的行数
        //当前页
        int currentPage = pb.getCurrentPage();
        //查询的起始行
        int index = (currentPage - 1) * pb.getPageCount();
        //返回查询的行数
        int count = pb.getPageCount();

        try {
            //SQL语句
            String sql = "select * from administrators limit ?,? ;";
            //创建连接对象
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, index);
            stmt.setInt(2, count);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Administrators lists = new Administrators();
                lists.setId(rs.getInt("id"));
                lists.setName(rs.getString("name"));
                lists.setPassword(rs.getString("password"));
                list.add(lists);
            }
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return (map);
        } catch (Exception ex) {
            ex.printStackTrace();
            return (null);
        } finally {
            //释放数据集对象
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            //释放语句对象
            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    //获取总数
    public int getTotalCount() throws Exception {
        PreparedStatement pStatement = null;
        ResultSet rSet = null;
        String sql = "select count(*) from administrators";
        int count = 0;
        try {
            pStatement = DBHelper.getConnection().prepareStatement(sql);
            rSet = pStatement.executeQuery();
            //从当前位置移动光标向前一行,获取第一列的值
            rSet.next();
            count = rSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放数据集对象
            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            //释放预编译语句对象
            if (pStatement != null) {
                try {
                    pStatement.close();
                    pStatement = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return (count);

    }
    //根据id获取指定用户
    public Administrators getone(Administrators administrators) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //连接数据库并执行查询,把结果集放到rs里面
            String sql = "select * from administrators where id=? ;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, administrators.getId());
            rs = stmt.executeQuery();

            if (rs.next()) {
                Administrators lists = new Administrators();
                lists.setId(rs.getInt("id"));
                lists.setName(rs.getString("name"));
                lists.setPassword(rs.getString("password"));
                return (lists);
            }
            return (null);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return (null);
    }

    //模糊查询管理员信息
    public Map<String, Object> search(PageBean pb, Administrators administrators) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ArrayList<Administrators> list = new ArrayList<>();

        //查询总记录数:设置到pb对象中
        int totalCount = getTotalCount();
        pb.setTotalCount(totalCount);

        //判断（预防当前页为首页或末页时，点击上一页或下一页出错）
        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }

        // 获取当前页：计算查询的起始行、返回的行数
        // 当前页
        int currentPage = pb.getCurrentPage();
        // 查询的起始行
        int index = (currentPage - 1) * pb.getPageCount();
        // 查询返回的行数
        int count = pb.getPageCount();
        try {
            String sql = "select * from administrators where name like concat('%',?,'%') limit ? , ?;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setString(1, administrators.getName());
            stmt.setInt(2, index);
            stmt.setInt(3, count);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Administrators lists = new Administrators();
                lists.setId(rs.getInt("id"));
                lists.setName(rs.getString("name"));
                lists.setPassword(rs.getString("password"));
                // 把一个管理员加入集合
                list.add(lists);
            }
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return (map);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return (null);
    }

    //添加管理员用户
    public void add(Administrators administrators) throws SQLException {
        String sql="insert into administrators(name,password) values(?,?);";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setString(1,administrators.getName());
            pstmt.setString(2,administrators.getPassword());
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            pstmt.close();
        }
    }

    //删除管理员用户
    public void delete(Administrators administrators) throws SQLException {
        String sql = "delete from administrators where id=?;";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setInt(1, administrators.getId());
            pstmt.execute();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    //修改管理员用户
    public void update(Administrators administrators) throws SQLException {
        String sql = "update administrators set name=?,password=? where id=? ;";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setString(1,administrators.getName());
            pstmt.setString(2,administrators.getPassword());
            pstmt.setInt(3,administrators.getId());
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            pstmt.close();
        }


    }
}